﻿using LinkSql;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using hospital_registration_system.Entity;

namespace hospital_registration_system.DAL
{
    public class ManagerDao
    {
        MySqlDataAdapter mySqlDataAdapter;

        /// <summary>
        /// 管理员登录
        /// </summary>
        /// <param name="tel">电话号</param>
        /// <param name="CheckerKey">密码</param>
        /// <returns>管理员对象</returns>
        public Manager Login(string tel, string CheckerKey)
        {
            Manager manager;
            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            try
            {
                connection.Open();
                string sql = "select * from manager where telephone='" + tel + "'and manager_password='" + CheckerKey + "'";
                mySqlDataAdapter = new MySqlDataAdapter(sql, connection);
                DataTable dataTable = new DataTable();
                mySqlDataAdapter.Fill(dataTable);
                if (dataTable.Rows.Count == 0)
                {
                    return null;
                }
                else
                {
                        manager = new Manager(
                        int.Parse(dataTable.Rows[0]["id"].ToString()),
                        dataTable.Rows[0]["nickname"].ToString(),
                        dataTable.Rows[0]["manager_name"].ToString(),
                        dataTable.Rows[0]["sex"].ToString(),
                        dataTable.Rows[0]["telephone"].ToString(),
                        dataTable.Rows[0]["email"].ToString(),
                        dataTable.Rows[0]["address"].ToString(),
                        dataTable.Rows[0]["manager_password"].ToString(),
                        dataTable.Rows[0]["created_by"].ToString(),
                        (DateTime)(dataTable.Rows[0]["created_time"]),
                        dataTable.Rows[0]["updated_by"].ToString(),
                        (DateTime)(dataTable.Rows[0]["updated_time"]),
                        int.Parse(dataTable.Rows[0]["state"].ToString())
                                        ); 

                }
            }
            catch (MySqlException)
            {
                return null;
            }
            finally
            {

                connection.Close();
            }
            return manager;
        }

        /// <summary>
        /// 通过对象增加管理员
        /// </summary>
        /// <param name="manager"></param>
        /// <returns></returns>
        public bool insert(Manager manager)
        {
            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            try
            {
                string sql = "insert into manager(nickname,manager_name,sex,telephone,email,address,manager_password,created_by,created_time,updated_by,updated_time,state) values("
                    +"'"+manager.Nickname+"'" + ","
                    + "'" + manager.Manager_name + "'"+","
                    + "'" + manager.Sex + "'" + ","
                    + "'" + manager.Telephone + "'" + ","
                    + "'" + manager.Email + "'" + ","
                    + "'" + manager.Address + "'" + ","
                    + "'" + manager.Manager_password + "'" + ","
                    + "'" + manager.CREATED_BY1 + "'" + ","
                    + "'" + manager.CREATED_TIME1 + "'" + ","
                    + "'" + manager.UPDATED_BY1 + "'" + ","
                    + "'" + manager.UPDATED_TIME1 + "'" + ","
                    + "'" + manager.State + "'"
                    +")";
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                cmd.ExecuteNonQuery();


            }
            catch (MySqlException)
            {
 
                return false;
            }
            finally
            {
                connection.Close();
            }

            return true;
        }

        /// <summary>
        /// 通过id删除信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool delete(string id)
        {
            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            string sql = "delete from manager where id = '" + id + "'";
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException)
            {
                return false;
            }
            finally
            {
                connection.Close();
            }
            return true;
        }

        /// <summary>
        /// 通过对象修改信息
        /// </summary>
        /// <param name="manager"></param>
        /// <returns></returns>
        public bool update(Manager manager)
        {
            
            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            string sql = "update manager set "
                + "nickname='" +manager.Nickname+"',"
                +"manager_name='" +manager.Manager_name + "',"+
                "sex='" +manager.Sex + "',"+
                "telephone='" +manager.Telephone + "'," +
                "email='" +manager.Email + "'," +
                "address='" +manager.Address + "'," +
                "manager_password='" +manager.Manager_password + "," +
                "created_by='" +manager.CREATED_BY1 + "'," +
                "created_time='" +manager.CREATED_TIME1 + "'," +
                "updated_by='" +manager.UPDATED_BY1 + "'," +
                "updated_time='" +manager.UPDATED_TIME1 + "'," +
                "state='"+manager.State + "' " +
                "where id = "+manager.Id
                ;
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(sql,connection);
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException)
            {
                return false;
            }
            finally
            {
                connection.Close();
            }
            return true;
            
        }


        /// <summary>
        /// 通过id查询信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Manager selectById(int id)
        {
            Manager manager;
            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            try
            {
                connection.Open();
                string sql = "select * from manager where id='" + id;
                mySqlDataAdapter = new MySqlDataAdapter(sql, connection);
                DataTable dataTable = new DataTable();
                mySqlDataAdapter.Fill(dataTable);
                if (dataTable.Rows.Count == 0)
                {
                    return null;
                }
                else
                {
                    manager = new Manager(
                    int.Parse(dataTable.Rows[0]["id"].ToString()),
                    dataTable.Rows[0]["nickname"].ToString(),
                    dataTable.Rows[0]["manager_name"].ToString(),
                    dataTable.Rows[0]["sex"].ToString(),
                    dataTable.Rows[0]["telephone"].ToString(),
                    dataTable.Rows[0]["email"].ToString(),
                    dataTable.Rows[0]["address"].ToString(),
                    dataTable.Rows[0]["manager_password"].ToString(),
                    dataTable.Rows[0]["created_by"].ToString(),
                    (DateTime)(dataTable.Rows[0]["created_time"]),
                    dataTable.Rows[0]["updated_by"].ToString(),
                    (DateTime)(dataTable.Rows[0]["updated_time"]),
                    int.Parse(dataTable.Rows[0]["state"].ToString())
                                    );

                }
            }
            catch (MySqlException)
            {
                return null;
            }
            finally
            {

                connection.Close();
            }
      
            return manager;
        }

    }
}
